using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VTXTKON
    {
        public void SetUpNewRow(ref DataRow dwReturn, DataRow dwInput)
        {
            #region
            #endregion
            switch (app.company)
            {
                default:
                case "YGR":
                case "TST":
                case "JLT":
                    #region=========
                    dwReturn["TXTKSL"] = " ".PadRight(0);
                    dwReturn["TXTKFILLER"] = " ".PadRight(2);
                    dwReturn["TXTKMANDANT"] = " ".PadRight(2);
                    dwReturn["TXTKPIND"] = " ".PadRight(0);
                    dwReturn["TXTKPOS"] = " ".PadRight(0);
                    dwReturn["TXTKATR"] = " ".PadRight(0);
                    dwReturn["TXTKTEXT"] = " ".PadRight(80);
                    #endregion
                    break;
            }
            INTEXDBC.MapData(true, ref dwReturn, dwInput);
        }
        public DataRow Convert2TXTKON(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryTXTKON(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
                    dwReturn["TXTKSL"] = " ".PadRight(0);
                    dwReturn["TXTKFILLER"] = " ".PadRight(2);
                    dwReturn["TXTKMANDANT"] = " ".PadRight(2);
                    dwReturn["TXTKPIND"] = " ".PadRight(0);
                    dwReturn["TXTKPOS"] = " ".PadRight(0);
                    dwReturn["TXTKATR"] = " ".PadRight(0);
                    dwReturn["TXTKTEXT"] = " ".PadRight(80);
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapTXTKON(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveTXTKON(DataRow dwData, string ITXDBVERSION = "YGR")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;

            setResult = QueryTXTKON(dwData, 4);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapTXTKON(ref dwLocal, dwData);
            dwLocal["TXTKSL"] = 16 + (dwLocal["TXTKTEXT"].ToString()).Trim().Length;
            if (!flgExist)
            {
                InsertTXTKON(dwLocal);
            }
            else
            {
                UpdateTXTKON(dwLocal);
            } 
        }
        public void InsertTXTKON(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            or1 = OracleDB.CreateCommand();
            switch (app.company)
            {
                default:
                    or1.CommandText = "INSERT INTO TXTKON ( TXTKSL,TXTKFILLER,TXTKMANDANT,TXTKPIND,TXTKPOS,TXTKATR,TXTKTEXT)"
                    + " VALUES (:TXTKSL:,:TXTKFILLER:,:TXTKMANDANT:,:TXTKPIND:,:TXTKPOS:,:TXTKATR:,:TXTKTEXT:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryTXTKON(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            string key = "";
            switch (option)
            {
                case 1: SResult = QTXT01(dwData); break;
                case 2: SResult = QTXT02(dwData); break;
                case 3: SResult = QTXT03(dwData); break;
                case 31: SResult = QTXT31(dwData); break;
                case 4: SResult = QTXT04(dwData); break;
                case 5: SResult = QTXT05(dwData); break;
                case -9: SResult = QTXT_9(dwData); break;
            }

            return SResult;
        }
        public DataSet QTXT_9(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT MAX(TXTKPOS) AS TXTKPOS FROM TXTKON "
                             + " WHERE TXTKMANDANT=:TXTKMANDANT: AND  TXTKPIND=:TXTKPIND: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QTXT01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT TXTISIND AS TXTISIND,TXTITXTART,TXTIPIND,TXTKPOS,TXTKTEXT,TXTKATR,TXTIAENDBEN,TXTIAENDDAT  "
            +" FROM TXTIND T JOIN TXTKON ON TXTIMANDANT=TXTKMANDANT AND  TXTIPIND=TXTKPIND"
                + " WHERE TXTIMANDANT=:TXTIMANDANT: AND TXTISIND=:TXTISIND: AND TXTITXTART=:TXTITXTART:"
                + " ORDER BY 1,2,3,4";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QTXT02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            string key = dwData["TXTISIND"].ToString();
            or1.CommandText = "SELECT TXTISIND AS TXTISIND,TXTITXTART,TXTIPIND,TXTKPOS,TXTKTEXT,TXTIAENDBEN,TXTIAENDDAT"
                + " FROM TXTIND T JOIN TXTKON ON TXTIMANDANT=TXTKMANDANT AND  TXTIPIND=TXTKPIND"
                + " WHERE TXTIMANDANT=:TXTIMANDANT:"
               + " AND TXTISIND LIKE '" + key + "%' AND TXTITXTART=:TXTITXTART:"
                + " ORDER BY 1,2,3,4";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }

        public DataSet QTXT03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT DISTINCT TXTISIND AS TXTISIND,TXTITXTART,TXTIPIND,TXTKPOS,TXTKTEXT,TXTIAENDBEN,TXTIAENDDAT FROM TXTIND T JOIN VKPOS "
                        + " P ON T.TXTIMANDANT=VKPMANDANT AND T.TXTITXTART='VAD' AND SUBSTR(T.TXTISIND,1,29)=VKPKOLL||VKPART"
                        + " AND TXTISPRACHKZ='  ' JOIN TXTKON K ON TXTIMANDANT=TXTKMANDANT AND  TXTIPIND=TXTKPIND"
                            + " WHERE VKPMANDANT=:VKPMANDANT: AND VKPSA='  ' AND VKPAUFKRS=:VKPAUFKRS: AND VKPAUFLFD=:VKPAUFLFD: "
                        + " ORDER BY 1,2,3,4";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QTXT31(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT DISTINCT TXTISIND AS TXTISIND,TXTITXTART,TXTIPIND,TXTKPOS,TXTKTEXT,TXTIAENDBEN,TXTIAENDDAT FROM TXTIND T JOIN VKPOS "
                       + " P ON T.TXTIMANDANT=VKPMANDANT AND T.TXTITXTART='VAD' AND SUBSTR(T.TXTISIND,1,29)=VKPKOLL||VKPART"
                       + " AND TXTISPRACHKZ='  ' JOIN TXTKON K ON TXTIMANDANT=TXTKMANDANT AND  TXTIPIND=TXTKPIND"
                              + " WHERE VKPMANDANT=:VKPMANDANT: AND VKPSA='  ' AND VKPAUFKRS=:VKPAUFKRS: AND VKPAUFLFD=:VKPAUFLFD: AND VKPAUFPOS=:VKPAUFPOS:"
                       + " ORDER BY 1,2,3,4";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }

        public DataSet QTXT04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM TXTKON "
                                + " WHERE TXTKMANDANT=:TXTKMANDANT: AND  TXTKPIND=:TXTKPIND: AND TXTKPOS=:TXTKPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QTXT05(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT TXTISIND AS TXTISIND,T.* FROM TXTKON T"
               + " JOIN TXTIND ON TXTIMANDANT=TXTKMANDANT AND  TXTIPIND=TXTKPIND"
                           + " WHERE TXTKMANDANT=:TXTKMANDANT: AND  TXTKPIND=:TXTKPIND: "
                           + dwData["TXTKFILLER"].ToString()
                           + " ORDER BY TXTKPOS";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QTXT051(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT TXTISIND AS TXTISIND,T.* FROM TXTKON T"
               + " JOIN TXTIND ON TXTIMANDANT=TXTKMANDANT AND  TXTIPIND=TXTKPIND"
                           + " WHERE TXTKMANDANT=:TXTKMANDANT:  "
                           + dwData["TXTKFILLER"].ToString()
                           + " ORDER BY TXTKPIND,TXTKPOS";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }

        public DataSet QTXT06(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "select TXTKTEXT from txtind T1, TXTKON T2"
                        + " where T1.txtitxtart = 'ABP' AND T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND  AND TRIM(T1.TXTISIND) ='" + dwData["ABPFILLER1"].ToString().Trim() + "'";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }


        public void DeleteTXTKON(DataRow dwData, int option = 1)
        { 
            switch (option)
            {
                case 1:
                    DTK01(dwData);
                    break;
                default:
                    break;
            } 
        }
        public void DTK01(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();

            or1.CommandText = "DELETE FROM TXTKON "
                     + "WHERE  TXTKMANDANT=:TXTKMANDANT: AND TXTKPIND=:TXTKPIND: AND TXTKPOS=:TXTKPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            Connect2Proxy(or1);
        }
        public void DTK02(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM TXTKON "
            + "WHERE  TXTKMANDANT=:TXTKMANDANT: AND TXTKPIND=:TXTKPIND: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DTK00(DataRow dwData)
        { 
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM TXTKON  "
            + "WHERE  EXISTS (SELECT 1 FROM TXTIND WHERE TXTIMANDANT=TXTKMANDANT AND TXTKPIND=TXTIPIND AND  TXTIMANDANT=:TXTIMANDANT: AND TXTITXTART=:TXTITXTART:)";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UpdateTXTKON(DataRow dwData, string ITXDBVERSION = "50.24")
        { 
            or1 = OracleDB.CreateCommand();
            switch (app.company)
            {
                default:
                    or1.CommandText = "UPDATE TXTKON SET TXTKSL=:TXTKSL:,TXTKFILLER=:TXTKFILLER:,TXTKMANDANT=:TXTKMANDANT:,TXTKPIND=:TXTKPIND:,TXTKPOS=:TXTKPOS:,TXTKATR=:TXTKATR:,TXTKTEXT=:TXTKTEXT:"
                    + "WHERE  TXTKMANDANT=:TXTKMANDANT: AND TXTKPIND=:TXTKPIND: AND TXTKPOS=:TXTKPOS:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }

        public override string BATCHINSERT(DataRow dwData)
        {
            string value = "";
            switch (app.company)
            {
                default:
                case "YCC":
                case "YGR":
                case "TST":
                    value = "INTO  TXTKON ( TXTKSL,TXTKFILLER,TXTKMANDANT,TXTKPIND,TXTKPOS,TXTKATR,TXTKTEXT)"
                         + " VALUES (:TXTKSL:,:TXTKFILLER:,:TXTKMANDANT:,:TXTKPIND:,:TXTKPOS:,:TXTKATR:,:TXTKTEXT:)";
                    value = INTEXDBC.InjectData(value, dwData);
                    break;
            }

            return value;
        }
    }
}
